package com.kingyun.gpsinspection.purificationservice.common;

import com.alibaba.fastjson.JSONObject;
import com.cqhilink.api.common.utils.UniqueUtil;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Created by Administrator on 2017/6/21/021.
 */
public class DataTest {

    private static final JSONObject analysisWay = new JSONObject();
    private static final String[] itemCodes = {"A","B","C","D","E","F","G","H","I","J","K"};

    private static void init(){
        analysisWay.put("气相色谱法",1);
        analysisWay.put("滴定分析",2);
        analysisWay.put("钼蓝法",3);
        analysisWay.put("在线分析",4);
        analysisWay.put("电化学分析",5);
        analysisWay.put("重量法",6);
        analysisWay.put("分光光度法",7);
        analysisWay.put("亚甲基蓝分光光度法",8);
        analysisWay.put("差减法",9);
    }


    public static void main(String[] args){
        init();
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            String url = "jdbc:oracle:" + "thin:@192.168.0.188:1521:ORCL";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
            String user = "zxjhc";// 用户名,系统默认的账户名
            String password = "zxjhc";// 你安装时选设置的密码
            con = DriverManager.getConnection(url, user, password);// 获取连接
            System.out.println("连接成功！");
            String sql1 = "insert into wwp_analysis(ANALYSIS_ID,ANALYSIS_CELL,ANALYSIS_CODE,ANALYSIS_NAME,ANALYSIS_WAY,ANALYSIS_FORMULA,IS_NEED,STATUS,UNIT,ORG_ID,DIGIT,IS_EFFECTIVE,ANALYSIS_TIMES)" +
                    "values(?,?,?,?,?,?,?,0,?,'XNNJ34j31uamyfnmwZw7SfSgxWg0hjBZ',?,?,1)";// 预编译语句，“？”代表参数
            String sql2 = "insert into wwp_analysis_items(ANALYSIS_ITEMS_ID,ANALYSIS_ID,ITEM_NAME,ITEM_CODE)values(?,?,?,?)";
            /*pre = con.prepareStatement(sql1);// 实例化预编译语句
            pre.setString(1, "刘显安");// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            pre.executeUpdate();// 执行查询，注意括号中不需要再加参数*/

            Workbook rwb=Workbook.getWorkbook(new File("E:\\公式记录表07191.xls"));
            Sheet rs=rwb.getSheet(0);//或者rwb.getSheet(0)
            int rows=rs.getRows();//得到所有的行

            for (int i = 1; i < rows; i++) {
                System.out.println("第"+i+"行！");
                int j=0;
                //第一个是列数，第二个是行数
                String ANALYSIS_ID = UniqueUtil.uuid();
                String ANALYSIS_CELL=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                String ANALYSIS_CODE=rs.getCell(j++, i).getContents();
                String ANALYSIS_NAME=rs.getCell(j++, i).getContents();
                String ANALYSIS_WAY=rs.getCell(j++, i).getContents();
                String ANALYSIS_FORMULA=rs.getCell(j++, i).getContents();
                System.out.println(ANALYSIS_CODE);
                String items = rs.getCell(j++, i).getContents();
                String UNIT = rs.getCell(j++, i).getContents();
                String DIGIT = rs.getCell(j++, i).getContents();
                String IS_EFFECTIVE = rs.getCell(j++, i).getContents();
                pre = con.prepareStatement(sql1);
                pre.setString(1,ANALYSIS_ID);
                pre.setString(2,ANALYSIS_CELL);
                pre.setString(3,ANALYSIS_CODE);
                pre.setString(4,ANALYSIS_NAME);
                pre.setInt(5,analysisWay.getInteger(ANALYSIS_WAY)==null?0:analysisWay.getInteger(ANALYSIS_WAY));
                pre.setString(6,ANALYSIS_FORMULA);
                if(  ANALYSIS_FORMULA.indexOf("M")>0 || ANALYSIS_FORMULA.indexOf("N")>0 || ANALYSIS_FORMULA.indexOf("S")>0 || ANALYSIS_FORMULA.indexOf("L")>0 || ANALYSIS_FORMULA.indexOf("Q")>0
                        || ANALYSIS_FORMULA.indexOf("P")>0 || ANALYSIS_FORMULA.indexOf("T")>0 || ANALYSIS_FORMULA.indexOf("R")>0 || ANALYSIS_FORMULA.indexOf("Z")>0
                        || ANALYSIS_FORMULA.indexOf("X")>0 || ANALYSIS_FORMULA.indexOf("W")>0){
                    pre.setInt(7,1);
                }else{
                    pre.setInt(7,0);
                }
                pre.setString(8,UNIT);
                pre.setInt(9,Integer.parseInt(DIGIT));
                pre.setInt(10,Integer.parseInt(IS_EFFECTIVE));
                int result = pre.executeUpdate();
                if( result > 0 ){
                    String[] item = items.split(",");
                    pre = con.prepareStatement(sql2);
                    for( int z=0;z<item.length;z++ ){
                        String ANALYSIS_ITEMS_ID = UniqueUtil.uuid();
                        String ITEM_NAME = item[z];
                        String ITEM_CODE = itemCodes[z];
                        pre.setString(1,ANALYSIS_ITEMS_ID);
                        pre.setString(2,ANALYSIS_ID);
                        pre.setString(3,ITEM_NAME);
                        pre.setString(4,ITEM_CODE);
                        System.out.println("items："+items);
                        System.out.println(ANALYSIS_ITEMS_ID+"，"+ANALYSIS_ID+","+ITEM_NAME+","+ITEM_CODE);
                        pre.executeUpdate();
                    }
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭！");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }

}
